package com.jsyso.jsyso.db;

import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Pattern;

import org.apache.commons.lang3.StringUtils;

import com.jsyso.jsyso.util.Arrays;

/**
 * 数据库方言
 * @author janjan, xujian_jason@163.com
 *
 */
public abstract class Dialect {
	public static final String KEY_TABLE = "table";
	public static final String KEY_DISTINCT = "distinct";
	public static final String KEY_FIELD = "field";
	public static final String KEY_JOIN = "join";
	public static final String KEY_WHERE = "where";
	public static final String KEY_GROUP = "group";
	public static final String KEY_HAVING = "having";
	public static final String KEY_ORDER = "order";
	public static final String KEY_LIMIT = "limit";
	public static final String KEY_PAGE = "page";
	public static final String KEY_UNION = "union";
	public static final String KEY_LOCK = "lock";
	public static final String KEY_COMMENT = "comment";
	public static final String KEY_FORCE = "force";
	public static final String KEY_INSERT = "insert";
	public static final String KEY_VALUES = "values";
	
	public static final String EXP_EQ = "eq";
	public static final String EXP_NEQ = "neq";
	public static final String EXP_GT = "gt";
	public static final String EXP_EGT = "egt";
	public static final String EXP_LT = "lt";
	public static final String EXP_ELT = "elt";
	public static final String EXP_NOTLIKE = "notlike";
	public static final String EXP_LIKE = "like";
	public static final String EXP_IN = "in";
	public static final String EXP_NOTIN = "notin";
	public static final String EXP_BETWEEN = "between";
	public static final String EXP_NOTBETWEEN = "notbetween";
	
	protected String selectSql = "SELECT%DISTINCT% %FIELD% FROM %TABLE%%FORCE%%JOIN%%WHERE%%GROUP%%HAVING%%ORDER%%LIMIT% %UNION%%LOCK%%COMMENT%";
	protected String insertSql = "%INSERT% INTO %TABLE%(%FIELD%) VALUES%VALUES%";
	protected String updateSql = "UPDATE %TABLE% SET %FIELD%%WHERE%";
	protected String deleteSql = "DELETE FROM %TABLE%%WHERE%";
	protected Map<String, String> expMap = new HashMap<String, String>();

	public Dialect() {
		expMap.put(EXP_EQ, "=");
		expMap.put(EXP_NEQ, "<>");
		expMap.put(EXP_GT, ">");
		expMap.put(EXP_EGT, ">=");
		expMap.put(EXP_LT, "<");
		expMap.put(EXP_ELT, "<=");
		expMap.put(EXP_NOTLIKE, "NOT LIKE");
		expMap.put(EXP_LIKE, "LIKE");
		expMap.put(EXP_IN, "IN");
		expMap.put(EXP_NOTIN, "NOT IN");
		expMap.put(EXP_BETWEEN, "BETWEEN");
		expMap.put(EXP_NOTBETWEEN, "NOT BETWEEN");
	}
	
	public String parseSelectSql(Options options) {
		return StringUtils.replaceEach(selectSql, new String[]{
			"%TABLE%", "%DISTINCT%", "%FIELD%",
			"%JOIN%", "%WHERE%", "%GROUP%",
			"%HAVING%", "%ORDER%", "%LIMIT%",
			"%UNION%","%LOCK%","%COMMENT%","%FORCE%"
		}, new String[]{
			this.parseTable(options.getTable()),
			this.parseDistinct(options.getDistinct()),
			this.parseField(options.getField()),
			this.parseJoin(options.getJoin()),
			this.parseWhere(options.getWhere()),
			this.parseGroup(options.getGroup()),
			this.parseHaving(options.getHaving()),
			this.parseOrder(options.getOrder()),
			this.parseLimit(options.getLimit(), options.getPage()),
			this.parseUnion(options.getUnion()),
			this.parseLock(options.getLock()),
			this.parseComment(options.getComment()),
			this.parseForce(options.getForce())
		});
	}
	
	public String parseUpdateSql(String table, Map<String, Object> fields, Wheres where) {
		if(StringUtils.isBlank(table) 
				|| fields == null || fields.isEmpty() 
				|| where == null || where.isEmpty()) {
			return "";
		}
		String[] fieldArray = new String[fields.size()];
		int index = 0;
		for(String key : fields.keySet()) {
			String whereString = null;
			Object value = fields.get(key);
			if(value.getClass().isArray()) {
				Object[] exps = (Object[]) value;
				whereString = this.pParseWhereExp(key, exps);
			} else {
				whereString = key + "=:" + key;
			}
			if(whereString != null) {
				fieldArray[index++] = whereString;
			}
		}
		return StringUtils.replaceEach(updateSql, new String[]{
			"%TABLE%", "%FIELD%", "%WHERE%"
		}, new String[]{
			deleteTableAS(table),
			StringUtils.join(fieldArray, ','),
			this.parseWhere(where)
		});
	}
	
	public String parseInsertSql(Map<String, String> map) {
		if(map == null || map.isEmpty()) {
			return "";
		}
		return StringUtils.replaceEach(insertSql, new String[]{
				"%INSERT%", "%TABLE%", "%FIELD%", "%VALUES%"
		}, new String[]{
				map.get(KEY_INSERT),
				deleteTableAS(map.get(KEY_TABLE)),
				map.get(KEY_FIELD),
				map.get(KEY_VALUES)
		});
	}
	
	public String parseDeleteSql(String table, Wheres where) {
		if(StringUtils.isBlank(table) 
				|| where == null || where.isEmpty()) {
			return "";
		}
		return StringUtils.replaceEach(deleteSql, new String[]{
				"%TABLE%", "%WHERE%"
		}, new String[]{
				deleteTableAS(table),
				this.parseWhere(where)
		});
	}
	
	protected String parseTable(String option) {
		return StringUtils.isNotBlank(option) ? option : "";
	}
	
	protected String deleteTableAS(String table) {
		if(StringUtils.isBlank(table)) 
			return "";
		String[] splitTables = StringUtils.split(table, " ");
		return splitTables[0];
	}
	
	protected String parseDistinct(boolean option) {
		return option ? " DISTINCT " : "";
	}
	
	protected String parseField(String option) {
		return StringUtils.isNotBlank(option) ? option : "*";
	}
	
	protected String parseJoin(List<String> option) {
		return option != null && !option.isEmpty() 
				? " " + StringUtils.join(option, ' ') + " " : "";
	}
	
	protected String parseWhere(Wheres option) {
		if(option == null || option.isEmpty()) {
			return "";
		}
		String[] wheres = new String[option.size()];
		int index = 0;
		for(String key : option.keySet()) {
//			if(StringUtils.isBlank(key)) {
//				continue ;
//			}
			if(key == null) 
				continue ;
			String whereString = null;
			Object value = option.get(key);
			if(value == null) {
				whereString = key + "=:" + key;
			}else {
				if(value instanceof String) {
					whereString = key + "='" + value + "'";
				}else if(value.getClass().isArray()) {
					Object[] exps = (Object[]) value;
					whereString = this.pParseWhereExp(key, exps);
				}else {
					whereString = key + "=" + value;
				}
			}
			if(whereString != null) {
				wheres[index++] = whereString;
			}
		}
		return index > 0 ? " WHERE " + StringUtils.join(wheres, " AND ") : "";
	}
	
	private String pParseWhereExp(String key, Object[] exps) {
		// 需要括号正则
		String regexLike = "^(notlike|like|notin|in)$";
		String exp = (String) exps[0];
		if(StringUtils.isBlank(exp)) {
			return "";
		}
		String whereString = key;
		Object expValue = exps[1];
		if(Pattern.compile(regexLike, Pattern.DOTALL).matcher(exp).find()) {
			whereString += " " + this.expMap.get(exp) + " (" + (expValue == null ? ":" + key : expValue) + ")";
		}else {
			whereString += this.expMap.containsKey(exp) ? ( 
					this.expMap.get(exp) + (expValue == null ? ":" + key : 
						(expValue instanceof String ? "'" + expValue + "'" : expValue) ) 
					) : expValue;
		}
		return whereString;
	}
	
	protected String parseGroup(String option) {
		return StringUtils.isBlank(option) ? "" : "  GROUP BY " + option;
	}
	
	protected String parseHaving(String option) {
		return StringUtils.isBlank(option) ? StringUtils.EMPTY : " HAVING " + option;
	}
	
	protected String parseOrder(String option) {
		return StringUtils.isBlank(option) ? StringUtils.EMPTY : " ORDER BY " + option;
	}
	
	protected String parseUnion(List<Object> option) {
		if(option == null) {
			return "";
		}
		String unionString = "";
		for(Object obj : option) {
			if(!obj.getClass().isArray()) {
				continue ;
			}
			Object[] unions = (Object[]) obj;
			String sql = (String) unions[0];
			Boolean isAll = (Boolean) unions[1];
			if(StringUtils.isNotBlank(sql))
				unionString = "UNION " + (isAll ? "ALL " : "") + sql;
		}
		return unionString;
	}
	
	protected String parseLock(Boolean option) {
		return (option != null && option) ? " FOR UPDATE " : StringUtils.EMPTY;
	}
	
	protected String parseComment(String option) {
		return StringUtils.isBlank(option) ? StringUtils.EMPTY : " /* " + option + " */";
	}
	
	protected String parseForce(String option) {
		return StringUtils.isBlank(option) ? StringUtils.EMPTY : " FORCE INDEX ( " + option + " ) ";
	}
	
	protected String parseLimit(int[] limits, int[] pages) {
		if(pages != null && pages.length > 0) {
			int pageNum = Arrays.get(pages, 0, 1), 	// 页码（默认第一页）
				pageRow = Arrays.get(pages, 1, 20); // 每页多少条记录（默认20条）
			pageNum = pageNum < 1 ? 1 : pageNum;
			pageRow = pageRow < 0 ? 20 : pageRow;
			limits = new int[]{ pageRow * (pageNum - 1), pageRow };
		}
		return this.handleLimit(limits);
	}
	
	protected abstract String handleLimit(int[] limits);
	
}
